package com.example.administrator.ksf_zty.util;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.administrator.ksf_zty.R;

import java.util.ArrayList;


/**
 * Created by Administrator on 2018/1/8.
 */

public class SQLiteHelper extends SQLiteOpenHelper {

    private static SQLiteDatabase sqLiteDatabase;

    /* 构造方法，调用父类SQLiteOpenHelper的构造函数
     * 参1：上下文环境
     * 参2:数据库名称(以.db结尾)
     * 参3：游标工厂(默认为null)
     * 参4：代表使用数据库模型版本的证书*/
    public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }
    /* 根据需要对SQLiteDatabase 的对象填充表和数据初始化
     * 该方法时在第一次创建的时候执行，实际上时第一次得到SQLiteDatabase对象的时侯才会调用这个方法 */
    @Override
    public void onCreate(SQLiteDatabase db) {
    }
    //创建数据库表
    public static void CreateTable()
    {
        // TODO 创建数据库后，创建设置表
        String createSQL = " create table if not exists SysSet(\n" +
                "IP varchar(20),\n" +
                "Port varchar(30),\n" +
                "I_WERKS varchar(30)\n" +
                ")";

        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建配方表
        createSQL = " create table if not exists Formula(" +
                " I_WERKS	varchar(10), " +
                "FormulaCode varchar(20), " +
                "FormulaName varchar(50)," +
                "Code varchar(20), " +
                "Qty decimal(18,5), " +
                "Is_H varchar(5), " +
                "PFormulaCode varchar(20)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建盐水房投料扫描数据表
        createSQL = " create table if not exists BrineJar(" +
                "I_WERKS varchar(10) NULL, " +
                "Stock varchar(10) NULL," +
                "FormulaName varchar(20) NULL," +
                "Jarcode varchar(10) NULL, " +
                "[Code] [varchar](50) NULL, " +
                "[Name] [varchar](50) NULL, " +
                "[Qty_TF] [decimal](18, 3) NULL, " +
                "[Qty_SY] [decimal](18, 3) NULL, " +
                "[DW] [varchar](10) NULL, " +
                "[PC] [varchar](20) NULL, " +
                "[Batch_Qty] [int] NULL, " +
                "[CodeFlag] [varchar](50) NULL, " +
                "[JarType] [varchar](20) NULL, " +
                "[JarFlag] [varchar](50) NULL, " +
                "[SCanTime] [datetime] NULL, " +
                "[SCanBy] [varchar](20) NULL" +
                ");";
        sqLiteDatabase.execSQL(createSQL);

        // TODO 创建数据库后，创建投料筐扫描数据表
        createSQL = "create table if not exists Basket_Scan( " +
                "[I_WERKS] [varchar](20) NULL, " +
                "[Stock] [varchar](20) NULL, " +
                "[Doucno] [varchar](20) NULL, " +
                "[Barcode] [varchar](50) NULL, " +
                "[Code] [varchar](30) NULL, " +
                "[Name] [varchar](50) NULL, " +
                "[Qty] [decimal](18, 3) NULL, " +
                "[DW] [varchar](10) NULL, " +
                "[PC] [varchar](20) NULL, " +
                "[Scan_Time] [datetime] NULL, " +
                "[Scan_By] [varchar](20) NULL" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建生产备料、生产退料、退回供应商、原物料调拨  SAP下载数据表
        createSQL = "create table if not exists MaterSap(" +
                "I_WERKS varchar(30)," +
                "Stock varchar(30)," +
                "Stock_to varchar(30)," +
                "BLDH varchar(30)," +
                "DDH varchar(30)," +
                "Code varchar(30)," +
                "Name varchar(30)," +
                "PC varchar(30)," +
                "DW varchar(30)," +
                "Qty decimal(18,3)," +
                "Kind varchar(30), " +
                "Islp varchar(30), " +
                "GRUND varchar(30), " +
                "QTYK decimal(18,3)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        Log.d("CreateTable",createSQL);
        // TODO 创建数据库后，创建生产备料、生产退料、退货、原物料调拨  本地扫描表
        createSQL = "create table if not exists Mater(" +
                "I_WERKS varchar(30), " +
                "Stock varchar(30), " +
                "Stock_to varchar(30), " +
                "BLDH varchar(30), " +
                "DDH varchar(30), " +
                "Barcode varchar(50), " +
                "Code varchar(30), " +
                "Name varchar(30), " +
                "PC varchar(30), " +
                "DW varchar(30), " +
                "Qty decimal(18,3)," +
                "Kind varchar(30), " +
                "Uno varchar(30), " +
                "Odate varchar(30), " +
                "ltype varchar(30), " +
                "Islp varchar(30), " +
                "ItemName varchar(50)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建半成品生产入库  SAP下载数据表
        createSQL = "create table if not exists Semi_InSap(" +
                "I_WERKS varchar(30)," +
                "Stock varchar(30)," +
                "Doucno varchar(30)," +
                "Code varchar(30)," +
                "Name varchar(30)," +
                "PC varchar(30)," +
                "DW varchar(30)," +
                "Qty decimal(18,3)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建半成品生产入库  本地扫描表
        createSQL = "create table if not exists Semi_In(" +
                "I_WERKS varchar(30)," +
                "Stock varchar(30)," +
                "Doucno varchar(30)," +
                "Barcode varchar(50)," +
                "Code varchar(30)," +
                "Name varchar(30)," +
                "PC varchar(30)," +
                "DW varchar(30)," +
                "Qty decimal(18,3)," +
                "userId varchar(30)," +
                "InTime varchar(30)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建关企出库SAP下载单据
        createSQL = "create table if not exists ProductSap(" +
                "I_WERKS	varchar(10),  " +
                "LGORT varchar(20), " +
                "I_VBELN varchar(20), " +
                "O_NAME1 varchar(40), " +
                "O_KUNNR varchar(10), " +
                "O_UPLOAD varchar(5), " +
                "MATNR varchar(20), " +
                "MAKTX varchar(50), " +
                "CHARG varchar(20), " +
                "VRKME varchar(5), " +
                "LFIMG decimal(18,5), " +
                "POSNR varchar(10)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        // TODO 创建数据库后，创建关企出库扫描表
        createSQL = "create table if not exists Product(" +
                "I_WERKS varchar(30)," +
                "Stock varchar(20), " +
                "Doucno varchar(30)," +
                "O_NAME1 varchar(50), " +
                "O_KUNNR varchar(20), " +
                "Barcode varchar(30), " +
                "Code varchar(30), " +
                "Name varchar(30), " +
                "PC varchar(30), " +
                "DW varchar(30), " +
                "Qty decimal(18,5), " +
                "Kind varchar(10), " +
                "Uno varchar(30), " +
                "Upload varchar(10), " +
                "Odate varchar(30)" +
                ");";
        sqLiteDatabase.execSQL(createSQL);
        Log.d("CreateTable", "数据表添加完成！");
    }



    /*
    将数据库从旧的模型转换为新的模型
    参1：对象 ; 参2：旧版本号 ; 参3：新版本号
     */
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO 更改数据库版本的操作

    }

    //打开数据库执行的函数
    public void onOpen(SQLiteDatabase db) {
        // TODO 每次成功打开数据库后首先被执行
        super.onOpen(db);

    }

    /*
    创建sqlite数据库
     */
    public static void createSQLite(Context context) {
        /* 创建一个MySQLiteOpenHelper，该语句执行是不会创建或打开连接的 */
        SQLiteOpenHelper helper = new SQLiteHelper(context, Config.DataBaseName, null, 1);
        /* 调用MySQLiteOpenHelper的getWriteableDatabase()方法，创建或者打开一个连接 */
        sqLiteDatabase = helper.getWritableDatabase();
        Log.d("CreateDataBase", Config.DataBaseName);
        CreateTable();//创建数据表
       Tips.show(context, "数据库创建成功", 1000);

    }


    /*
    * sqlite查询数据
     */
    public static ArrayList query(Context context, String table, String column, String condition,
                                  String condition_value) {
        /* 获取SQLiteDatabase的对象 */
        SQLiteOpenHelper helper = new SQLiteHelper(context, Config.DataBaseName, null, 1);
        sqLiteDatabase = helper.getReadableDatabase();

        /* 调用SQLiteDatabase的query()方法进行查询，返回一个Cursor对象：由数据库查询返回的结果集对象
        * 参1 String：表名
        * 参2 String[]:需要查询的列;
        * 参3 String :查询条件;
        * 参4 String[]:查询条件的参数;
        * 参5 String: 对查询的结果进行分组;
        * 参6 String: 对分组结果进行限制;
        * 参7 String: 对查询结果进行排序;
        * */
//        Cursor cursor = sqLiteDatabase.query("User", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
        Cursor cursor = sqLiteDatabase.query(table, new String[]{column},
                condition == null ? null : condition + "=?",
                condition == null ? null : new String[]{condition_value}, null, null, null);
        /* 保存结果集中对应字段的数据 */
        ArrayList values = new ArrayList();
//        values.add("请选择");
        /* 从结果集中读取数据 */
        while (cursor.moveToNext()) {
            values.add(cursor.getString(cursor.getColumnIndex(column)));
        }
        return values;
    }

}